package model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import model.bean.Coquan;

public class CoquanDAO extends BaseDAO {

	/**
	 * get idcoquan
	 * 
	 * @return int idcoaquan
	 * 
	 *         TriLD
	 */
	public int getDonviID() {
		openDatabase();
		qry = "SELECT idCQ FROM mockdb.cqdieutra ORDER BY idCQ DESC LIMIT 1";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			int id;
			while (rs.next()) {
				id = rs.getInt("idCQ");
				int donviID = id + 1;
				return donviID;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return 1;
	}

	/**
	 * Thêm mới cơ quan
	 * 
	 * @return boolean
	 * 
	 *         TriLD
	 */
	public boolean themCoquan(int id, String tencq, String diachi,
			String dienthoai, String email) {
		boolean success = false;
		openDatabase();
		qry = "INSERT INTO mockdb.cqdieutra (idCQ,TenCQ,DiaChi,SDT,email) VALUES('"
				+ id
				+ "',N'"
				+ tencq
				+ "',N'"
				+ diachi
				+ "', '"
				+ dienthoai
				+ "', N'" + email + "')";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			success = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return success;

	}

	/**
	 * Hiển thị danh sách cơ quan
	 * 
	 * @return ArrayList<dsCoquan>
	 * 
	 *         TriLD
	 */
	public ArrayList<Coquan> dsCoquan() {

		ArrayList<Coquan> dsCoquan = new ArrayList<Coquan>();
		openDatabase();
		qry = "SELECT * FROM mockdb.cqdieutra order by idCQ asc";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			while (rs.next()) {

				Coquan cq = new Coquan();

				cq.setIdcq(rs.getString("idCQ"));
				cq.setTencq(rs.getString("TenCQ"));
				cq.setDiachi(rs.getString("DiaChi"));
				cq.setSdt(rs.getString("SDT"));
				cq.setEmail(rs.getString("email"));

				dsCoquan.add(cq);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return dsCoquan;
	}

	public boolean xoaCoquan(String id) {
		openDatabase();
		qry = "DELETE FROM mockdb.cqdieutra WHERE idCQ = '" + id + "'";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public Coquan getCoquan(String id) {
		Coquan cq = new Coquan();
		openDatabase();
		qry = "SELECT * FROM mockdb.cqdieutra where idCQ = '" + id + "'";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			while (rs.next()) {

				cq.setIdcq(rs.getString("idCQ"));
				cq.setTencq(rs.getString("TenCQ"));
				cq.setDiachi(rs.getString("DiaChi"));
				cq.setSdt(rs.getString("SDT"));
				cq.setEmail(rs.getString("email"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cq;
	}

	public boolean capNhatCQ(String id, String tencq, String diachi,
			String dienthoai, String email) {
		openDatabase();
		qry = "UPDATE mockdb.cqdieutra SET TenCQ = '" + tencq
				+ "',DiaChi = N'" + diachi + "', SDT = '" + dienthoai + "', email = N'" + email + "' WHERE idCQ = '" + id + "'";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}
}
